package smartcommunityserver.mapper;



import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import smartcommunityserver.bean.CarCountBean;

import java.util.List;

/**
 * @author renmin
 */
@Mapper
public interface CarCountMapper {
    @Select("SELECT\n" +
            "    '驶入' as name,\n" +
            "    round(in_cnt/total*100,2) as zb\n" +
            "\n" +
            "FROM(SELECT DATE_FORMAT(window_start,'%Y-%m-%d') as time\n" +
            "          ,SUM(in_cnt) as in_cnt\n" +
            "          ,SUM(out_cnt) as out_cnt\n" +
            "          ,SUM(in_cnt+out_cnt) as total\n" +
            "     FROM dws_sca.dws_summary_cars_log\n" +
            "where  DATE_FORMAT(window_start, '%Y-%m-%d') = '2025-08-21'\n" +
            "     GROUP BY DATE_FORMAT(window_start,'%Y-%m-%d')\n" +
            "    )a1\n" +
            "UNION ALL\n" +
            "SELECT\n" +
            "    '驶出' as name,\n" +
            "    round(out_cnt/total*100,2) as zb\n" +
            "\n" +
            "FROM(SELECT DATE_FORMAT(window_start,'%Y-%m-%d') as time\n" +
            "          ,SUM(in_cnt) as in_cnt\n" +
            "          ,SUM(out_cnt) as out_cnt\n" +
            "          ,SUM(in_cnt+out_cnt) as total\n" +
            "     FROM dws_sca.dws_summary_cars_log\n" +
            "     where  DATE_FORMAT(window_start, '%Y-%m-%d') = '2025-08-21'\n" +
            "     GROUP BY DATE_FORMAT(window_start,'%Y-%m-%d')\n" +
            "    )a1")
    List<CarCountBean> carCountMapper();
}
